﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NHibernate;
using NHibernate.Linq;
using NHibernate.Criterion;
using System.Linq.Expressions;
using System.Reflection;
using System.Linq.Dynamic;
using Entidades;
using Common;
using NHibernate.SqlTypes;
using NHibernate.Type;
using NHibernate.Util;
using Persistence;
using NHibernate.Transform;

namespace Persistencia.Repositorios
{
    public class RepositorioBase<T>  where T : Entidad
    {
        #region Private Members
        private readonly ISession _session;
        #endregion

        #region Constructors
        public RepositorioBase()
        {
            try
            {
                _session = NHibernateSessionHelper.GetSession();
            }
            catch (Exception)
            {
                throw;
            }
        }
        #endregion

        #region Public Methods

        /// <summary>
        /// Devuelve la primer ocurrencia de la Entidad solicitada
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public T ObtenerEntidad()
        {
            T entidad = null;
            try
            {
                using (var transaction = _session.BeginTransaction())
                {
                    entidad = _session.Query<T>().FirstOrDefault();
                }
            }
            catch (Exception)
            {
                throw;
            }
            return entidad;
        }

        /// <summary>
        /// Returns an Entity based on a given lambda expression as a filter (which will be transformed to a WHERE clause)
        /// </summary>
        /// <param name="expression"></param>
        /// <returns></returns>
        public T ObtenerEntidad(Func<T, bool> expression)
        {
            T entity = null;
            try
            {
                using (var transaction = _session.BeginTransaction())
                {
                    entity = _session.Query<T>().FirstOrDefault(expression);
                }
            }
            catch (Exception)
            {
                throw;
            }
            return entity;            
        }

        /// <summary>
        /// Devuelve una Entidad, dado su Id (PK)
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public T ObtenerEntidad(int id)
        {
            T entidad = null;
            try
            {
                using (ITransaction transaction = _session.BeginTransaction())
                {
                    entidad = _session.Query<T>().FirstOrDefault(x => x.Id == id);
                }
            }
            catch (Exception)
            {
                throw;
            }
            return entidad;
        }

        /// <summary>
        /// Devuelve una Entidad, dado el valor de su campo "Nombre"
        /// </summary>
        /// <param name="nombre"></param>
        /// <returns></returns>
        public T ObtenerEntidad(string nombre)
        {
            T entidad = null;
            try
            {
                using (ITransaction transaction = _session.BeginTransaction())
                {
                    entidad = _session.Query<T>().FirstOrDefault(x => x.Nombre.ToUpper() == nombre.ToUpper());
                }
            }
            catch (Exception)
            {
                throw;
            }
            return entidad;
        }

        /// <summary>
        /// Devuelve el total de una Entidad, según un filtro
        /// </summary>
        /// <param name="filtro"></param>
        /// <returns></returns>
        public int ObtenerTotalEntidades(string filtro)
        {
            int total = 0;
            try
            {
                using (var transaction = _session.BeginTransaction())
                {
                    if (filtro.Length == 0)
                        filtro = "1 == 1";

                    total = _session.Query<T>()
                        .Where(filtro)
                        .Count();
                }
            }
            catch (Exception)
            {
                throw;
            }
            return total;
        }

        /// <summary>
        /// Returns the total amount of Entities based on a given lambda expression as a filter (which will be transformed to a WHERE clause)
        /// </summary>
        /// <param name="expression"></param>
        /// <returns></returns>
        public int ObtenerTotalEntidades(Func<T, bool> expression)
        {
            var amount = 0;
            try
            {
                using (var transaction = _session.BeginTransaction())
                {
                    amount = _session.Query<T>().Where(expression).Count();
                }
            }
            catch (Exception)
            {
                throw;
            }
            return amount;            
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public List<T> ObtenerEntidades()
        {
            var list = new List<T>();
            try
            {
                using (var transaction = _session.BeginTransaction())
                {
                    list = _session.Query<T>().ToList();
                }
            }
            catch (Exception)
            {
                throw;
            }
            return list;            
        }

        /// <summary>
        /// Devuelve todos las Entidades, según un filtro y paginación
        /// </summary>
        /// <param name="paginacion"></param>
        /// <param name="filtro"></param>
        /// <returns></returns>
        public List<T> ObtenerEntidades(Data.Pagination paginacion = null, string filtro = null, string sortColumn = "Id", string sortOrder = "asc")
        {
            var lista = new List<T>();
            try
            {
                using (ITransaction transaction = _session.BeginTransaction())
                {
                    if (string.IsNullOrEmpty(filtro))
                        filtro = "1 == 1";

                    if ( paginacion == null )
                    {
                        #region Non-paged query
                        lista = _session.Query<T>()
                                .Where(filtro)
                                .OrderBy(sortColumn + " " + sortOrder)
                                .ToList();
                        #endregion

                    }
                    else
                    {
                        #region Paged query
                        lista = _session.Query<T>()
                                .Where(filtro)
                                .OrderBy(paginacion.SortColumn + " " + paginacion.SortOrder)
                                .Skip((paginacion.CurrentPage - 1) * paginacion.PageSize)
                                .Take(paginacion.PageSize)
                                .ToList();
                        #endregion
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }
            return lista;
        }

        /// <summary>
        /// Returns an Entity based on a given lambda expression as a filter (which will be transformed to a WHERE clause)
        /// </summary>
        /// <param name="expression"></param>
        /// <returns></returns>
        public List<T> ObtenerEntidades(Func<T, bool> expression)
        {
            var list = new List<T>();
            try
            {
                using (var transaction = _session.BeginTransaction())
                {
                    list = _session.Query<T>().Where(expression).ToList();
                }
            }
            catch (Exception)
            {
                throw;
            }
            return list;            
        }

        /// <summary>
        /// Guarda una Entidad
        /// </summary>
        /// <param name="entidad"></param>
        /// <returns></returns>
        public bool GuardarEntidad(Entidad entidad)
        {
            var resultado = false;
            try
            {
                using (var transaction = _session.BeginTransaction())
                {
                    _session.SaveOrUpdate(entidad);
                    _session.Flush();
                    transaction.Commit();
                    resultado = true;
                }
            }
            catch (Exception)
            {
                throw;
            }
            return resultado;    
        }

        /// <summary>
        /// Elimina una Entidad
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool EliminarEntidad(int id)
        {
            bool resultado = false;
            try
            {
                using (var transaction = _session.BeginTransaction())
                {
                    var entidad = _session.Query<T>().FirstOrDefault(x => x.Id == id);
                    if (entidad != null)
                    {
                        _session.Delete(entidad);
                        _session.Flush();
                        transaction.Commit();
                        resultado = true;
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }
            return resultado; 
        }

        /// <summary>
        /// Elimina una Entidad
        /// </summary>
        /// <param name="entidad"></param>
        /// <returns></returns>
        public bool EliminarEntidad(Entidad entidad)
        {
            bool resultado = false;
            try
            {
                using (var transaction = _session.BeginTransaction())
                {
                    _session.Delete(entidad);
                    _session.Flush();
                    transaction.Commit();
                    resultado = true;
                }
            }
            catch (Exception)
            {
                throw;
            }
            return resultado; 
        }

        /// <summary>
        /// Elimina una lista de Entidades, según un tipo de entidad y una condicion
        /// </summary>
        /// <param name="tipoEntidad"></param>
        /// <param name="condicion"></param>
        /// <returns></returns>
        public bool EliminarEntidades(Type tipoEntidad, string condicion)
        {
            bool resultado = false;
            try
            {
                using (ITransaction transaction = _session.BeginTransaction())
                {
                    _session.Delete(string.Format("from {0} where {1}", tipoEntidad.Name, condicion));
                    _session.Flush();
                    transaction.Commit();
                    resultado = true;
                }
            }
            catch (Exception)
            {
                throw;
            }
            return resultado; 
        }

        #endregion

        public class StoredProcedure
        {
            #region Private Members
            private readonly ISession _session;
            #endregion

            #region Constructors
            public StoredProcedure()
            {
                _session = NHibernateSessionHelper.GetSession();
            }
            #endregion
            
            #region Public Methods
            public T GetObject(string sp, Dictionary<string, object> parameters = null)
            {
                T result = null;
                try
                {
                    var list = this.GetList(sp, parameters);
                    if (list != null)
                        result = list.First();
                }
                catch (Exception)
                {
                    throw;
                }
                return result;
            }
            public IList<T> GetList(string sp, Dictionary<string, object> parameters = null)
            {
                IList<T> list = null;
                using (ITransaction transaction = _session.BeginTransaction())
                {
                    list = this.ExecuteProcedure(sp, parameters).List<T>();
                }
                return list;
            }
            public IList<T> GetList(string sp, int pageSize, int currentPage, Dictionary<string, object> parameters = null)
            {
                var _parameters = new Dictionary<string, object>
                {
                    {"page_size", pageSize},
                    {"current_page", currentPage}
                };
                if ( parameters != null && parameters.Count > 0 )
                {
                    foreach (var p in parameters)
                    {
                        _parameters.Add(p.Key.ToLower(), p.Value);
                    }
                }
                return this.GetList(sp, _parameters);
            }
            #endregion

            #region Private Methods
            private IQuery AddStoredProcedureParameters(IQuery query, Dictionary<string, object> parameters)
            {
                if (parameters == null)
                    return query;

                foreach (var param in parameters)
                {
                    var value = param.Value;
                    if (value == DBNull.Value || value == null)
                        query.SetParameter(param.Key, null, NHibernate.Type.TypeFactory.GetSerializableType(typeof(DBNull)));
                    else
                        query.SetParameter(param.Key, value, NHibernate.Type.TypeFactory.GetSerializableType(value.GetType()));
                }

                return query;
            }
            private ISQLQuery ExecuteProcedure(string storedProcedureName, Dictionary<string, object> parameters = null)
            {
                var query = String.Format("exec dbo.{0} ", storedProcedureName);

                if ( parameters != null && parameters.Count > 0)
                    query += string.Join(", ", parameters.Keys.Select(key => String.Format(":{0}", key)).ToArray());
                
                var sqlQuery = _session.CreateSQLQuery(query).AddEntity(typeof(T));
                
                this.AddStoredProcedureParameters(sqlQuery, parameters);
                
                return sqlQuery;
            }
            #endregion
        }
    }
}
